alter table [contract]
add CardNumber varchar(50) null,
	CardDate varchar(20) null,
	CardCVV varchar(10) null,
	CardCC varchar(10) null
go
ALTER PROCEDURE [dbo].[spContractEdit]
(
	@ContractId int,
	@ContactId int,
	@TemplateId int,
	@Status varchar(50),
	@AgentId int,
	
	@CardNumber varchar(50) = null,
	@CardDate varchar(20) = null,
	@CardCVV varchar(10) = null,
	@CardCC varchar(10) = null,
	
	@TemplateFieldId varchar(max),
	@ContactColumnName varchar(max),
	@ContactColumnValue varchar(max)
)
AS
BEGIN
	set nocount on
	set xact_abort on
		
    begin transaction
		if (@ContractId = -1)
		begin
			insert into [Contract]
			(ContactId, TemplateId, [Status], AgentId, CardNumber, CardDate, CardCVV, CardCC)
			values (@ContactId, @TemplateId, @Status, @AgentId, @CardNumber, @CardDate, @CardCVV, @CardCC)
			
			set @ContractId = SCOPE_IDENTITY()
		end
		else begin
			update [Contract]
			set TemplateId = @TemplateId, CardNumber = @CardNumber, CardDate = @CardDate, CardCVV = @CardCVV, CardCC = @CardCC
			where (ContractId = @ContractId)
		end
		
		delete 
		from ContractField
		where (ContractId = @ContractId)
		
		insert into ContractField
		(ContractId, TemplateFieldId, ContactColumnName, ContactColumnValue)
		select @ContractId, vwTemplate.Item, vwColumnName.Item, vwColumnValue.Item 
		from dbo.fnStringSplit(@TemplateFieldId, ';') as vwTemplate inner join dbo.fnStringSplit(@ContactColumnName, ';') as vwColumnName on vwTemplate.IdItem = vwColumnName.IdItem
																	inner join dbo.fnStringSplit(@ContactColumnValue, ';') as vwColumnValue on vwTemplate.IdItem = vwColumnValue.IdItem

	commit transaction
	
	select @ContractId as ContractId
END
go

	